﻿Imports System.Data.SqlClient

Public Class frmAppraisalForm

    Sub FillPerformanceAttributesList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From PerformanceAttributesList", cnn)
            Dim Reader As SqlDataReader

            Me.GridAppraisalForm.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridAppraisalForm.Rows.Add({Reader.Item("AttributeEng"), Reader.Item("AttributeAr")})
            End While
            Reader.Close()
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtName.Clear()
        Me.txtJobTitle.Clear()
        Me.txtBranch.Clear()
        Me.txtHireDate.Clear()
        Me.txtPeriodHiredYears.Clear()
        Me.txtPeriodHiredMonths.Clear()
        Me.CombAppraisalType.SelectedIndex = -1
        Me.GridAppraisalForm.Rows.Clear()
        Me.txtComments.Clear()
        Me.txtRecommendation.Clear()
    End Sub

    Private Sub ListUsers_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListUsers.SelectedIndexChanged
        Clear()

        If Me.ListUsers.SelectedItems.Count > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select EmpNo,FullNameEn,Position,Branch,HireDate,GetDate() Today " & _
                                          "From StaffProfiles where EmpNo=" & CInt(Me.ListUsers.SelectedItems(0).Text), cnn)
                Dim Reader As SqlDataReader
                Dim Today As Date
                Dim PeriodMonths As Integer

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtEmpNo.Text = Reader.Item("EmpNo")
                    Me.txtName.Text = Reader.Item("FullNameEn")
                    Me.txtJobTitle.Text = Reader.Item("Position")
                    Me.txtHireDate.Text = CDate(Reader.Item("HireDate")).ToString("dd-MMM-yyyy")
                    Me.txtBranch.Text = Reader.Item("Branch")
                    Today = CDate(Reader.Item("Today"))
                End While
                Reader.Close()
                cnn.Close()

                PeriodMonths = DateDiff(DateInterval.Month, CDate(Me.txtHireDate.Text), Today)
                Me.txtPeriodHiredYears.Text = (PeriodMonths - (PeriodMonths Mod 12)) / 12
                Me.txtPeriodHiredMonths.Text = PeriodMonths Mod 12

                FillPerformanceAttributesList()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        Me.CombAppraisalType.SelectedIndex = -1
        FillPerformanceAttributesList()
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.txtName.Text.Trim.Length = 0 Then
            MsgBox("Please select a staff member from the list")

        ElseIf Me.CombAppraisalType.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombAppraisalType, "Please fill in all required details")
            Me.CombAppraisalType.Focus()

        ElseIf Me.txtComments.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtComments, "Please fill in all required details")
            Me.txtComments.Focus()

        ElseIf Me.txtRecommendation.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtRecommendation, "Please fill in all required details")
            Me.txtRecommendation.Focus()
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                'Validate rows
                Dim Valid As Boolean = True
                For Each row As DataGridViewRow In Me.GridAppraisalForm.Rows
                    If row.Cells(Me.DGColEvaluation.Index).Value = "" Then
                        row.ErrorText = "Please fill in all fields"
                        Valid = False
                    Else
                        row.ErrorText = ""
                    End If
                Next

                If Valid = False Then
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                Dim MoveNo As Integer
                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From StaffAppraisalForm"
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                For Each row As DataGridViewRow In Me.GridAppraisalForm.Rows
                    cmd.CommandText = "Insert into StaffAppraisalForm " & _
                                      "(MoveNo,EmpID,EmpName,Position,Branch,HireDate,HiredYears,HiredMonths,AppraisalType," & _
                                      "PerformanceAttributeEng,PerformanceAttributeAr,Evaluation,Comments,Recommendation,CreatedBy) " & _
                                      "Values " & _
                                      "(@MoveNo,@EmpID,@EmpName,@Position,@Branch,@HireDate,@HiredYears,@HiredMonths,@AppraisalType," & _
                                      "@PerformanceAttributeEng,@PerformanceAttributeAr,@Evaluation,@Comments,@Recommendation,@CreatedBy)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    cmd.Parameters.AddWithValue("@EmpID", Me.txtEmpNo.Text)
                    cmd.Parameters.AddWithValue("@EmpName", Me.txtName.Text)
                    cmd.Parameters.AddWithValue("@Position", Me.txtJobTitle.Text)
                    cmd.Parameters.AddWithValue("@Branch", Me.txtBranch.Text)
                    cmd.Parameters.AddWithValue("@HireDate", CDate(Me.txtHireDate.Text))
                    cmd.Parameters.AddWithValue("@HiredYears", CInt(Me.txtPeriodHiredYears.Text))
                    cmd.Parameters.AddWithValue("@HiredMonths", CInt(Me.txtPeriodHiredMonths.Text))
                    cmd.Parameters.AddWithValue("@AppraisalType", Me.CombAppraisalType.Text)
                    cmd.Parameters.AddWithValue("@PerformanceAttributeEng", row.Cells(Me.DGColAttributeEng.Index).Value)
                    cmd.Parameters.AddWithValue("@PerformanceAttributeAr", row.Cells(Me.DGColAttributeAr.Index).Value)
                    cmd.Parameters.AddWithValue("@Evaluation", row.Cells(Me.DGColEvaluation.Index).Value)
                    cmd.Parameters.AddWithValue("@Comments", Me.txtComments.Text.Trim)
                    cmd.Parameters.AddWithValue("@Recommendation", Me.txtRecommendation.Text.Trim)
                    cmd.Parameters.AddWithValue("@CreatedBy", CurrentUser)
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                PrintStaffAppraisalForm(MoveNo)

                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End Try
        End If
    End Sub

    Sub FillStaff()
        Try
            Me.Cursor = Cursors.Default

            Dim cmd As New SqlCommand("select EmpNo,FullNameEn from StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()
            Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmAppraisal_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillStaff()
    End Sub
End Class